package dao

import (
	"database/sql"
	"haiqiu-go-admin/entity"
	"haiqiu-go-admin/entity/dto"
	"haiqiu-go-admin/utils"
	"log"
	"strconv"
	"strings"
)

func SelectRoleById(id int64) (*entity.SysRole, error) {
	row := DB.QueryRow("select * from sys_role where id = ?", id)
	var sysRole entity.SysRole
	err := row.Scan(
		&sysRole.Id,
		&sysRole.Sign,
		&sysRole.Name,
		&sysRole.Desc,
		&sysRole.Icon,
		&sysRole.Active,
		&sysRole.Sort,
		&sysRole.Del,
		&sysRole.CreateTime,
		&sysRole.UpdateTime,
	)
	if err != nil {
		return nil, err
	}
	return &sysRole, nil
}

func RoleByUserId(userId int64) (*[]entity.SysRole, error) {
	rows, err := DB.Query("SELECT sr.* FROM `sys_user_role` sur "+
		"INNER JOIN sys_role sr on sr.id = sur.role_id "+
		"WHERE sur.user_id = ?", userId)
	if err != nil {
		return nil, err
	}
	var sysRoles []entity.SysRole
	for rows.Next() {
		var sysRole entity.SysRole
		err := rows.Scan(
			&sysRole.Id,
			&sysRole.Sign,
			&sysRole.Name,
			&sysRole.Desc,
			&sysRole.Icon,
			&sysRole.Active,
			&sysRole.Sort,
			&sysRole.Del,
			&sysRole.CreateTime,
			&sysRole.UpdateTime,
		)
		if err != nil {
			return nil, err
		}
		sysRoles = append(sysRoles, sysRole)
	}
	return &sysRoles, nil
}

// RoleById 根据ID查询数据
func RoleById(id int64) (*entity.SysRole, error) {
	row := DB.QueryRow("select * from sys_role where id = ?", id)
	var sysRole entity.SysRole
	err := row.Scan(
		&sysRole.Id,
		&sysRole.Sign,
		&sysRole.Name,
		&sysRole.Desc,
		&sysRole.Icon,
		&sysRole.Active,
		&sysRole.Sort,
		&sysRole.Del,
		&sysRole.CreateTime,
		&sysRole.UpdateTime,
	)
	if err != nil {
		panic(err)
	}
	return &sysRole, nil
}

// RolePageCount 分页查询总条数
func RolePageCount(page *dto.RoleQueryDto) (count int64) {
	wrapper := utils.QueryWrapper().Count("sys_role")
	getRoleCondition(page, wrapper)
	err := DB.QueryRow(wrapper.GetSql(), wrapper.GetVal()...).Scan(&count)
	if err != nil {
		log.Println(err.Error())
	}
	return count
}

func getRoleCondition(page *dto.RoleQueryDto, wrapper *utils.Wrapper) {
	if page.Params.Name != nil {
		wrapper.Like("name", *page.Params.Name)
	}
	if page.Params.Active != nil {
		wrapper.Eq("active", *page.Params.Active)
	}
	if page.Params.Sign != nil {
		wrapper.Like("sign", *page.Params.Sign)
	}
	if page.Params.BeginTime != nil && page.Params.EndTime != nil {
		wrapper.Between("create_time", *page.Params.BeginTime,
			*page.Params.EndTime)
	}
}

// RolePageList 分页查询数据
func RolePageList(page *dto.RoleQueryDto) (*[]entity.SysRole, error) {
	wrapper := utils.QueryWrapper().Page("sys_role", page.PageIndex, page.PageSize)
	getRoleCondition(page, wrapper)
	rows, err := DB.Query(wrapper.GetSql(), wrapper.GetVal()...)
	if err != nil {
		log.Println(err.Error())
		return nil, err
	}
	var sysRoles []entity.SysRole
	for rows.Next() {
		var sysRole = entity.SysRole{}
		err := rows.Scan(
			&sysRole.Id,
			&sysRole.Sign,
			&sysRole.Name,
			&sysRole.Desc,
			&sysRole.Icon,
			&sysRole.Active,
			&sysRole.Sort,
			&sysRole.Del,
			&sysRole.CreateTime,
			&sysRole.UpdateTime,
		)
		if err != nil {
			log.Println(err.Error())
			return nil, err
		}
		sysRoles = append(sysRoles, sysRole)
	}
	return &sysRoles, nil
}

// RoleAdd 新增数据
func RoleAdd(sysRole *entity.SysRole, tx *sql.Tx) (int64, error) {
	stmt, err := tx.Prepare("insert into `sys_role` " +
		"(" +
		"`id`, `sign`, `name`, `desc`, `icon`, `active`, `sort`, `del`, `create_time`, `update_time` " +
		") " +
		"values " +
		"(" +
		"?, ?, ?, ?, ?, ?, ?, ?, ?, ? " +
		")")
	exec, err := stmt.Exec(
		&sysRole.Id,
		&sysRole.Sign,
		&sysRole.Name,
		&sysRole.Desc,
		&sysRole.Icon,
		&sysRole.Active,
		&sysRole.Sort,
		&sysRole.Del,
		&sysRole.CreateTime,
		&sysRole.UpdateTime,
	)
	if err != nil {
		if err := tx.Rollback(); err != nil {
			log.Println(err.Error())
		}
		return 0, err
	}
	lastInsertId, err := exec.LastInsertId()
	if err != nil {
		return 0, err
	}
	return lastInsertId, nil
}

// RoleUpdate 根据ID更新数据
func RoleUpdate(sysRole *entity.SysRole, tx *sql.Tx) (int64, error) {
	stmt, err := tx.Prepare("update `sys_role` set " +
		"`sign` = ?, `name` = ?, `desc` = ?, `icon` = ?, " +
		"`active` = ?, `sort` = ?, `del` = ?, " +
		"`create_time` = ?, `update_time` = ? " +
		"where `id` = ?")
	exec, err := stmt.Exec(
		&sysRole.Sign,
		&sysRole.Name,
		&sysRole.Desc,
		&sysRole.Icon,
		&sysRole.Active,
		&sysRole.Sort,
		&sysRole.Del,
		&sysRole.CreateTime,
		&sysRole.UpdateTime,
		&sysRole.Id,
	)
	if err != nil {
		return 0, err
	}
	rowsAffected, err := exec.RowsAffected()
	if err != nil {
		return 0, err
	}
	return rowsAffected, nil
}

// RoleDelete 根据ID删除数据
func RoleDelete(id int64) (int64, error) {
	exec, err := DB.Exec("delete from sys_role where id = ?")
	if err != nil {
		panic(err)
		return 0, err
	}
	rowsAffected, err := exec.RowsAffected()
	if err != nil {
		panic(err)
		return 0, err
	}
	return rowsAffected, nil
}

// RoleDeleteBatch 根据ID批量删除数据
func RoleDeleteBatch(ids []int64) (int64, error) {
	var idsStr string
	for index, id := range ids {
		if index == len(ids)-1 {
			idsStr += strconv.FormatInt(id, 10)
		} else {
			idsStr += strconv.FormatInt(id, 10) + ", "
		}
	}
	exec, err := DB.Exec("delete from sys_role where id in (?)", idsStr)
	if err != nil {
		panic(err)
		return 0, err
	}
	rowsAffected, err := exec.RowsAffected()
	if err != nil {
		panic(err)
		return 0, err
	}
	return rowsAffected, nil
}

// RoleList 查询所有数据
func RoleList() (*[]entity.SysRole, error) {
	rows, err := DB.Query("select * from sys_role")
	if err != nil {
		panic(err)
		return nil, err
	}
	var sysRoles []entity.SysRole
	for rows.Next() {
		var sysRole entity.SysRole
		err := rows.Scan(
			&sysRole.Id,
			&sysRole.Sign,
			&sysRole.Name,
			&sysRole.Desc,
			&sysRole.Icon,
			&sysRole.Active,
			&sysRole.Sort,
			&sysRole.Del,
			&sysRole.CreateTime,
			&sysRole.UpdateTime,
		)
		if err != nil {
			panic(err)
			return nil, err
		}
		sysRoles = append(sysRoles, sysRole)
	}
	return &sysRoles, nil
}

func DeleteUserByRole(userId int64) error {
	_, err := DB.Exec("delete from sys_user_role where user_id in (?)", userId)
	if err != nil {
		panic(err)
		return err
	}
	return nil
}

func AddUserByRole(userId int64, roleIds []int64) (int64, error) {
	var sqlStr = "insert into sys_user_role (user_id,role_id) values "
	val := make([]any, 0)
	for index, roleId := range roleIds {
		if index == len(roleIds)-1 {
			sqlStr += "(?,?)"
		} else {
			sqlStr += "(?,?),"
		}
		val = append(val, userId, roleId)
	}
	stmt, err := DB.Prepare(sqlStr)
	if err != nil {
		return 0, err
	}
	exec, err := stmt.Exec(val...)
	if err != nil {
		return 0, err
	}
	lastInsertId, err := exec.LastInsertId()
	if err != nil {
		return 0, err
	}
	return lastInsertId, nil
}

func ActiveRoleById(sysRoles []entity.SysRole) (any, error) {
	var ids strings.Builder
	var active = sysRoles[0].Active
	for index, value := range sysRoles {
		if index == len(sysRoles) {
			ids.WriteString(strconv.FormatInt(value.Id, 10))
		} else {
			ids.WriteString(strconv.FormatInt(value.Id, 10))
		}
	}
	exec, err := DB.Exec("update sys_role set active = ? where id in ( ? )", active, ids.String())
	if err != nil {
		panic(err)
		return nil, err
	}
	rowsAffected, err := exec.RowsAffected()
	if err != nil {
		panic(err)
		return nil, err
	}
	return rowsAffected, nil
}

func DeleteMenuByRoleId(roleId int64, tx *sql.Tx) error {
	_, err := tx.Exec("delete from sys_role_permission where role_id = ?", roleId)
	if err != nil {
		panic(err)
		return err
	}
	return nil
}

func AddMenuByRoleId(roleId int64, menuIds []int64, tx *sql.Tx) error {
	sqlStr := "insert into sys_role_permission (role_id,permission_id) values "
	var vals []any
	for index, menuId := range menuIds {
		if index == len(menuIds)-1 {
			sqlStr += "(?, ?)"
		} else {
			sqlStr += "(?, ?), "
		}
		roleId := strconv.FormatInt(roleId, 10)
		menuId := strconv.FormatInt(menuId, 10)
		vals = append(vals, roleId, menuId)
	}
	stmt, err := tx.Prepare(sqlStr)
	_, err = stmt.Exec(vals...)
	if err != nil {
		log.Println(err.Error())
		return err
	}
	return nil
}

func AllocatedCount(allocatedDto *dto.AllocatedDto) (count *int64, err error) {
	stmt, values := condition(allocatedDto, true)
	err = DB.QueryRow(stmt, values...).Scan(&count)
	if err != nil {
		panic(err)
		return nil, err
	}
	return count, nil
}

func AllocatedPage(allocatedDto *dto.AllocatedDto) (*[]entity.SysUser, error) {
	sql, args := condition(allocatedDto, false)
	rows, err := DB.Query(sql, args...)
	if err != nil {
		log.Println(err.Error())
		return nil, err
	}
	var sysUsers []entity.SysUser
	for rows.Next() {
		var sysUser = entity.SysUser{}
		err := rows.Scan(
			&sysUser.Id,
			&sysUser.Username,
			&sysUser.Nickname,
			&sysUser.Email,
			&sysUser.Phone,
			&sysUser.Active,
			&sysUser.CreateTime,
		)
		if err != nil {
			log.Println(err.Error())
			return nil, err
		}
		sysUsers = append(sysUsers, sysUser)
	}
	return &sysUsers, nil
}
func UnAllocatedCount(allocatedDto *dto.AllocatedDto) (count *int64, err error) {
	sql, values := conditionNotIn(allocatedDto, true)
	err = DB.QueryRow(sql, values...).Scan(&count)
	if err != nil {
		panic(err)
		return nil, err
	}
	return count, nil
}

func UnAllocatedPage(allocatedDto *dto.AllocatedDto) (*[]entity.SysUser, error) {
	sql, args := conditionNotIn(allocatedDto, false)
	rows, err := DB.Query(sql, args...)
	if err != nil {
		log.Println(err.Error())
		panic(err)
		return nil, err
	}
	var sysUsers []entity.SysUser
	for rows.Next() {
		var sysUser = entity.SysUser{}
		err := rows.Scan(
			&sysUser.Id,
			&sysUser.Username,
			&sysUser.Nickname,
			&sysUser.Email,
			&sysUser.Phone,
			&sysUser.Active,
			&sysUser.CreateTime,
		)
		if err != nil {
			log.Println(err.Error())
			return nil, err
		}
		sysUsers = append(sysUsers, sysUser)
	}
	return &sysUsers, nil
}

// 根据参数条件生成sql
// count 是否为统计总条数，true查询分页总条数，false分页查询
func condition(allocatedDto *dto.AllocatedDto, count bool) (string, []any) {
	var sql strings.Builder
	var values []any
	if count {
		sql.WriteString("SELECT count(su.id) FROM sys_user_role sur " +
			"INNER JOIN sys_user su ON su.id = sur.user_id where 1=1 ")
	} else {
		sql.WriteString("SELECT su.id,su.username,su.nickname,email,su.phone,su.active,su.create_time AS createTime " +
			"FROM sys_user_role sur " +
			"INNER JOIN sys_user su ON su.id = sur.user_id where 1=1 ")
	}

	if allocatedDto.RoleId != nil {
		sql.WriteString("and sur.role_id = ? ")
		values = append(values, allocatedDto.RoleId)
	}
	if allocatedDto.Username != nil {
		sql.WriteString(" and su.username like %?% ")
		values = append(values, allocatedDto.Username)
	}
	if allocatedDto.Phone != nil {
		sql.WriteString(" and su.phone like %?% ")
		values = append(values, allocatedDto.Phone)
	}
	if allocatedDto.Email != nil {
		sql.WriteString(" and su.email like %?% ")
		values = append(values, allocatedDto.Email)
	}
	if !count {
		sql.WriteString("limit ?,? ")
		values = append(values, (allocatedDto.PageIndex-1)*allocatedDto.PageSize, allocatedDto.PageSize)
	}
	s := sql.String()
	return s, values
}

// 根据参数条件生成sql
// count 是否为统计总条数，true查询分页总条数，false分页查询
func conditionNotIn(allocatedDto *dto.AllocatedDto, count bool) (string, []any) {
	var sql strings.Builder
	var values []any
	if count {
		sql.WriteString("SELECT count(id) FROM sys_user WHERE id NOT IN ( " +
			"SELECT su.id FROM sys_user_role sur " +
			"INNER JOIN sys_user su ON su.id = sur.user_id " +
			"WHERE sur.role_id = ? ) ")
		values = append(values, allocatedDto.RoleId)
	} else {
		sql.WriteString("SELECT id,username,nickname,email,phone,active,create_time AS createTime " +
			"FROM sys_user WHERE id NOT IN ( " +
			"SELECT su.id FROM sys_user_role sur " +
			"INNER JOIN sys_user su ON su.id = sur.user_id " +
			"WHERE sur.role_id = ? ) ")
		values = append(values, allocatedDto.RoleId)
	}

	if allocatedDto.Username != nil {
		sql.WriteString(" and username like %?% ")
		values = append(values, allocatedDto.Username)
	}
	if allocatedDto.Phone != nil {
		sql.WriteString(" and phone like %?% ")
		values = append(values, allocatedDto.Phone)
	}
	if allocatedDto.Email != nil {
		sql.WriteString(" and email like %?% ")
		values = append(values, allocatedDto.Email)
	}
	if !count {
		sql.WriteString("limit ?,? ")
		values = append(values, (allocatedDto.PageIndex-1)*allocatedDto.PageSize, allocatedDto.PageSize)
	}
	s := sql.String()
	return s, values
}
